package bibliOnline.db;

/**
 *
 * @author Gabriele
 */
import java.util.*;
import java.sql.*;
import bibliOnline.util.details.BookData;
import java.util.logging.Level;
import java.util.logging.Logger;

public class BookSelector {

    /**
     * Ricerca semplice.
     * 
     * @param title
     * @param author
     * @return lista di libri corrispondente ai parametri
     */
    public static ArrayList<BookData> simpleDBSearch(String title, String author) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        ArrayList<BookData> bookList = new ArrayList(2);

        String sql = "select book_id, title, author, publisher, edition, genre, year, state from book"
                + " where lower(title) like ? and lower(author) like ?";
        try {
            pst = DBConnection.prepareStatement(sql);
            pst.setString(1, "%" + title.toLowerCase() + "%");
            pst.setString(2, "%" + author.toLowerCase() + "%");
            rs = pst.executeQuery();

            // Aggiungo la lista dei libri trovati
            while (rs.next()) {
                bookList.add(new BookData(
                        rs.getInt("book_id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getString("publisher"),
                        rs.getString("edition"),
                        rs.getString("genre"),
                        rs.getInt("year"),
                        rs.getString("state"),
                        0,"","")); // Sistemare TODO
            }

        } catch (Exception e) {
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
        }

        return bookList;
    }

    /**
     * Ricerca avanzata.
     * 
     * @param title
     * @param author
     * @param publisher
     * @param genre
     * @param year
     * @param state
     * @return lista di libri corrispondente ai parametri
     */
    public static ArrayList advancedDBSearch(String title, String author, String publisher, String genre, String year, String state) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        ArrayList<BookData> bookList = new ArrayList(2);
        String availability = "";

        // Filtro sullo stato
        if (state.equals("on")) {
            availability = " and state='AVAILABLE'";
        }

        String sql = "select book_id, title, author, publisher, edition, genre, year, state from book"
                + " where lower(title) like ? and lower(author) like ? and lower(publisher) like ?"
                + " and lower(genre) like ? and year like ?" + availability;
        try {
            pst = DBConnection.prepareStatement(sql);
            pst.setString(1, "%" + title.toLowerCase() + "%");
            pst.setString(2, "%" + author.toLowerCase() + "%");
            pst.setString(3, "%" + publisher.toLowerCase() + "%");
            pst.setString(4, "%" + genre.toLowerCase() + "%");
            pst.setString(5, "%" + year + "%");
            rs = pst.executeQuery();

            // Aggiungo la lista dei libri trovati
            while (rs.next()) {
                bookList.add(new BookData(
                        rs.getInt("book_id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getString("publisher"),
                        rs.getString("edition"),
                        rs.getString("genre"),
                        rs.getInt("year"),
                        rs.getString("state"),0,"","" // TODO
                        ));
            }

        } catch (Exception e) {
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
        }

        return bookList;
    }

    /**
     * Restituisce i dati di un libro, data l'id
     * 
     * @param book_id Id del libro
     * @return Dati del libro
     * @throws Exception Nessun libro trovato
     */
    public static ArrayList<BookData> bookDataSearch(String book_id) throws Exception {
        
        // Controllo se il codice è numerico
        Integer.parseInt(book_id);

        ResultSet rs = null;
        PreparedStatement pst = null;
        String sql = "select book_id, title, author, publisher, edition, genre, year, state"
                + " from book where book_id=?";
        ArrayList<BookData> bookList = new ArrayList(1);

        pst = DBConnection.prepareStatement(sql);
        pst.setString(1, book_id);
        rs = pst.executeQuery();
        if (rs.next()) {
            bookList.add(new BookData(
                    rs.getInt("book_id"),
                    rs.getString("title"),
                    rs.getString("author"),
                    rs.getString("publisher"),
                    rs.getString("edition"),
                    rs.getString("genre"),
                    rs.getInt("year"),
                    rs.getString("state"),0,"",""));
        } else {
            throw new Exception("Non ci sono libri con questo codice");
        }
        
        // Controllo se il libro è stato già prestato
        if (bookList.get(0).getState().equals("LENT")) {
            throw new Exception("Il libro è già stato prestato");
        }
        
        return bookList;
    }
    
    /*
    public static int getRemainingTime(String book_id) {
        String sql = "VALUES(GET_WAITING_TIME(" + book_id + "))";
        PreparedStatement ps;
        try {
            ps = DBConnection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.next();
            return rs.getInt(1);
        } catch (Exception ex) {
            Logger.getLogger(BookSelector.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }*/
}
